package com.classes;

import java.util.ArrayList;
import java.util.TreeMap;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import org.json.simple.*;

import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;

public class NotificationFactory {
	private static String message;
	
	public static String getMessage() {
		return "[NotificationFactory] " + NotificationFactory.message; 
	}
	
	public static ArrayList<Notification> getMyNotification(String userId, long case_id){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		ArrayList<Notification> nArr = new ArrayList<Notification>();
		PreparedStatement statement = null;
		try {
			String sql = "select * from notification "
					+ "join casehist on n_hist_id = ch_id "
					+ "where n_user_id = ? "
					+ "	and n_case_id = ? "
					+ "	and n_status = 'Active' ";
			
			statement = conn.prepareStatement(sql);
			statement.setString(1, userId);
			statement.setLong(2, case_id);
			
			ResultSet resultSet = statement.executeQuery();
			
			while(resultSet.next()){
				Notification n = new Notification();
				n.setNtfId(Integer.parseInt(resultSet.getString("n_id")));
				n.setInfo("type", resultSet.getString("n_type"));
				n.setInfo("note", resultSet.getString("ch_note"));
				n.setInfo("content", resultSet.getString("n_content"));
				n.setInfo("status", resultSet.getString("n_status"));
				n.setInfo("case_id", resultSet.getString("n_case_id"));
				n.setInfo("created_by", resultSet.getString("n_created_by"));
				n.setInfo("created_date", ((Timestamp) resultSet.getTimestamp("n_created_date")).getTime() + "");
				
				nArr.add(n);
			}
			
			return nArr;
			
		} catch (Exception e){
			NotificationFactory.message = e.getMessage();
			return null;
		} finally {
        	if (statement != null) {
        		try { statement.close(); } catch (Exception e) {}
        	}
			connMgr.freeConnection(connectionPool, conn);
		}
	}
	
	public static ArrayList<Notification> getNotifications(String userId, TreeMap<String, String> search) {

        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		ArrayList<Notification> nArr = new ArrayList<Notification>();
		PreparedStatement statement = null;
		try {
			String q_search = "";
			
			if(search != null) {
				if(search.containsKey("case_id") && search.get("case_id").length() > 0){
					q_search += "and c_reference_no = ? ";
				}
				
				if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
					q_search += "and upper(c_merchant_details) like ? ";
				}
				
				if(search.containsKey("application_type") && search.get("application_type").length() > 0){
					q_search += "and c_type = ? ";
				}
				
				if(search.containsKey("status") && search.get("status").length() > 0){
					q_search += "and n_status = ? ";
				}
				
				if(search.containsKey("from_date") && search.get("from_date").length() > 0){
					q_search += "and n_created_date >=  to_date(?, 'YYYY-MM-DD') ";
				}
			}
			
			String sql = "select * from notification "
					+ "join casemast on n_case_id = c_id "
					+ "where n_user_id = ? "
					+ q_search;
			
			statement = conn.prepareStatement(sql);
			statement.setString(1, userId);
			
			if(search != null) {
				int last_index = 1;
				
				if(search.containsKey("case_id") && search.get("case_id").length() > 0){
					++last_index;
					statement.setString(last_index, search.get("case_id").toUpperCase());
				}
				
				if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
					++last_index;
					statement.setString(last_index, "%\"REGISTERED_NAME\":\"" + search.get("merchant_name").toUpperCase() + "%");
				}
				
				if(search.containsKey("application_type") && search.get("application_type").length() > 0){
					++last_index;
					statement.setString(last_index, search.get("application_type"));
				}
				
				if(search.containsKey("status") && search.get("status").length() > 0){
					++last_index;
					statement.setString(last_index, search.get("status"));
				}
				
				if(search.containsKey("from_date") && search.get("from_date").length() > 0){
					++last_index;
					statement.setString(last_index, search.get("from_date"));
				}
			}
			
			ResultSet resultSet = statement.executeQuery();
			
			while(resultSet.next()){
				Notification n = new Notification();
				n.setNtfId(Integer.parseInt(resultSet.getString("n_id")));
				n.setInfo("type", resultSet.getString("n_type"));
				n.setInfo("content", resultSet.getString("n_content"));
				n.setInfo("status", resultSet.getString("n_status"));
				n.setInfo("app_type", resultSet.getString("c_type"));
				n.setInfo("case_reference_no", resultSet.getString("c_reference_no"));
				n.setInfo("merchant_id", resultSet.getString("c_merchant_id"));
				n.setInfo("case_id", resultSet.getString("c_id"));
				n.setInfo("created_by", resultSet.getString("n_created_by"));
				n.setInfo("created_date", ((Timestamp) resultSet.getTimestamp("n_created_date")).getTime() + "");
				
				JSONObject merchant_json = (JSONObject)JSONValue.parse(resultSet.getString("c_merchant_details"));
				n.setInfo("registered_name", merchant_json.get("registered_name").toString());
				n.setInfo("registered_no", merchant_json.get("registered_no").toString());
				
				nArr.add(n);
			}
			
			return nArr;
			
		} catch (Exception e){
			NotificationFactory.message = e.getMessage();
			return null;
		} finally {
        	if (statement != null) {
        		try { statement.close(); } catch (Exception e) {}
        	}
			connMgr.freeConnection(connectionPool, conn);
		}
	}

}